package cn.yunhe.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.yunhe.entity.Majors;
import cn.yunhe.util.DButil;

public class MajorsDaoImpl implements IMajorsDao {

	public List<Majors> getAllMajorsByRate() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Majors> list = new ArrayList<Majors>();

		try {
			conn = DButil.getCon();
			String sql = "select rownum,majors,employment_rate,majors_number "
					+ "from majors order by employment_rate desc";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Majors mj = new Majors();
				mj.setMajorsid(rs.getInt("rownum"));
				mj.setMajors(rs.getString("majors"));
				mj.setEmployment_rate(rs.getInt("employment_rate"));
				mj.setMajors_number(rs.getInt("majors_number"));
				
				list.add(mj);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DButil.close(conn, pstmt, rs);
		}
		return list;
	}

	public int addMajors(Majors majors) {
		String sql = "insert into majors values(seq_sys_majors.nextval,?,?,?)";
		Object[] pramas = {majors.getMajors(),majors.getEmployment_rate(),majors.getMajors_number()};
		
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}

	public int delMajorsById(int majorsid) {
		String sql = "delete from majors where majorsid = ?";
		Object[] pramas = {majorsid};
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}

	public int editMajorsById(Majors majors) {
		String sql = "update majors set majors = ? ,employment_rate = ? ,majors_number=? where majorsid = ?";
		Object[] pramas = {majors.getMajors(),majors.getEmployment_rate(),majors.getMajors_number(),majors.getMajorsid()};
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}

	public Majors getMajorsById(int majorsid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Majors majors = new Majors();

		try {
			conn = DButil.getCon();
			String sql = "select * from majors where majorsid = ?";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			majors.setMajorsid(rs.getInt("majorsid"));
			majors.setMajors(rs.getString("majors"));
			majors.setEmployment_rate(rs.getInt("employment_rate"));
			majors.setMajors_number(rs.getInt("majors_number"));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DButil.close(conn, pstmt, rs);
		}
		return majors;

	}

}
